﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Web;
using System.Text;
using System.Reflection;
using Microsoft.Practices.EnterpriseLibrary.Data;

namespace Lswweb.SqlServerDAL
{
    public class DataAccess
    {
        private static Database adoHelper;
        public static Database AdoHelper()
        {
            if (adoHelper == null)
                adoHelper = DatabaseFactory.CreateDatabase("ConnectionStr");
            return adoHelper;
        }

        #region 基础操作

        public static int ExecuteNonQuery(string sqlStr, params IDataParameter[] parameters)
        {
            DbCommand cmd = CreateCommand(sqlStr, parameters);
            return AdoHelper().ExecuteNonQuery(cmd);
        }

        public static object ExecuteScalar(string sqlStr, params IDataParameter[] parameters)
        {
            DbCommand cmd = CreateCommand(sqlStr, parameters);
            return AdoHelper().ExecuteScalar(cmd);
        }

        public static IDataReader ExecuteReader(string sqlStr, params IDataParameter[] parameters)
        {
            DbCommand cmd = CreateCommand(sqlStr, parameters);
            return AdoHelper().ExecuteReader(cmd);
        }

        public static DbCommand CreateCommand(string sqlStr)
        {
            return CreateCommand(sqlStr, null);
        }
        public static DbCommand CreateCommand(string sqlStr, params IDataParameter[] parameters)
        {
            if (string.IsNullOrEmpty(sqlStr))
                throw new Exception("sqlStr参数不能为空。");

            DbCommand cmd = AdoHelper().GetSqlStringCommand(sqlStr);
            if (parameters != null)
            {
                foreach (IDataParameter parameter in parameters)
                {
                    if (parameter != null)
                        cmd.Parameters.Add(((ICloneable)parameter).Clone());

                    //((ICloneable)parameter).Clone()执行一个深copy，不然会出现 当在一段程序中两次调command并且两个command有相同的参数时会报 SqlParameterCollection 已经包含xx个参数的错误。
                }
            }
            return cmd;
        }

        #endregion

        #region 常规 Sql 操作

        /// <summary>
        /// 获取记录条数
        /// </summary>
        /// <param name="tableName">数据表名</param>
        /// <param name="where">条件语句</param>
        /// <returns>返回记录条数</returns>
        public static int GetRecord(string declare, string tableName, string where, params IDataParameter[] parameters)
        {
            if(string.IsNullOrEmpty(tableName))
                throw new Exception("tableName参数不能为空。");

            if (!string.IsNullOrEmpty(declare) && declare.EndsWith(";"))
                declare = declare.TrimEnd(';') + ";";

            string sql = declare + "select count(*) from " + tableName;

            if (!string.IsNullOrEmpty(where))
                sql += " where " + where;

            return Convert.ToInt32(ExecuteScalar(sql, parameters));
        }
        public static int GetRecord(string tableName, string where, params IDataParameter[] parameters)
        {
            return GetRecord(string.Empty,tableName,where,parameters);
        }

        /// <summary>
        /// 更改排序字段的排序位置
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="displayOrderFieldName">排序字段名</param>
        /// <param name="identityName">主键字段名</param>
        /// <param name="where">条件语句，用来查询同级排序位置的条件语句。例如：ParentId=3</param>
        /// <param name="parameters">sql语句所使用到的参数，新排序位置参数为名@ + DispalyOrderName</param>
        public static bool UpdateDisplayOrder(string tableName, string displayOrderName, string identityName, string where, int newDisplayOrder, params IDataParameter[] parameters)
        {
            string identityParameter=AdoHelper().BuildParameterName(identityName);
            string displayOrderParameter = AdoHelper().BuildParameterName(displayOrderName);
 
            StringBuilder sql = new StringBuilder();
            if (newDisplayOrder <= 0)
            {
                sql.Append("declare @MaxDisplayOrder int;");
                sql.Append("select @MaxDisplayOrder = max(" + displayOrderName + ") from " + tableName);
                if (!string.IsNullOrEmpty(where))
                    sql.Append(" where " + where);
                sql.Append(";if @MaxDisplayOrder is null begin set @MaxDisplayOrder=0 end;");
                sql.Append("set @MaxDisplayOrder=@MaxDisplayOrder + 1;");
                sql.Append("update " + tableName + " set " + displayOrderName + " = @MaxDisplayOrder where " + identityName + " = " + identityParameter + ";");
            }
            else
            {
                sql.Append("declare @OldDisplayOrder int;declare @MaxDisplayOrder int;");
                sql.Append("select @OldDisplayOrder=" + displayOrderName + " from " + tableName + " where " + identityName + "=" + identityParameter + ";");
                sql.Append("if @OldDisplayOrder=" + displayOrderParameter + " begin return end;");
                sql.Append("select @MaxDisplayOrder = max(" + displayOrderName + ") from " + tableName);
                if (!string.IsNullOrEmpty(where))
                    sql.Append(" where " + where);
                sql.Append(";if @MaxDisplayOrder is null begin set @MaxDisplayOrder=0 end;");
                sql.Append("if " + displayOrderParameter + " >= @MaxDisplayOrder ");
                sql.Append("begin set " + displayOrderParameter + " = @MaxDisplayOrder + 1;");
                sql.Append("update " + tableName + " set " + displayOrderName + " = " + displayOrderParameter + " where " + identityName + " = " + identityParameter + " ");
                sql.Append("end ");
                sql.Append("else begin ");
                sql.Append("update " + tableName + " set " + displayOrderName + " = " + displayOrderName + " + 1 where " + displayOrderName + " >= " + displayOrderParameter);
                if (!string.IsNullOrEmpty(where))
                    sql.Append(" and " + where);
                sql.Append(";update " + tableName + " set " + displayOrderName + " = " + displayOrderParameter + " where " + identityName + " = " + identityParameter + ";");
                sql.Append("update " + tableName + " set " + displayOrderName + "=" + displayOrderName + " -1 where " + displayOrderName + ">@OldDisplayOrder");
                if (!string.IsNullOrEmpty(where))
                    sql.Append(" and " + where);
                sql.Append(" end;");
            }
            
            return Convert.ToBoolean(DataAccess.ExecuteNonQuery(sql.ToString(), parameters));

        }

        /// <summary>
        ///根据主键Id，交换两条记录的某个字段的值
        /// </summary>
        /// <param name="tableName">数据表名</param>
        /// <param name="changeFieldName">交换字段名</param>
        /// <param name="changeFieldType">交换字段的SqlType，例如:varcahar(64)</param>
        /// <param name="identityName">主键名</param>
        /// <param name="identityParemeterName">数据库记录一主键参数名称</param>
        /// <param name="identity2ParemeterName">数据库记录二主键参数名称</param>
        /// <param name="parameters">sql语句使用到的参数</param>
        public static bool ExchangeFieldValue(string tableName, string changeFieldName, string changeFieldType, string identityName, object id1,object id2)
        {
            if (string.IsNullOrEmpty(tableName) || string.IsNullOrEmpty(changeFieldName) || string.IsNullOrEmpty(changeFieldType) || string.IsNullOrEmpty(identityName))
                return false;

            string identity2ParameterName="@Id2";
            string identityParameterName="@Id1";
            IDataParameter[] parameters = new IDataParameter[2];
            parameters[0] = CreateParameter(identityParameterName, id1);
            parameters[1] = CreateParameter(identity2ParameterName, id2);

            StringBuilder sql = new StringBuilder();
            sql.Append("declare @ChangeValue " + changeFieldType + ";");
            sql.Append("select @ChangeValue=" + changeFieldName + " from " + tableName + " where " + identityName + "=" + identityParameterName + ";");
            sql.Append("update " + tableName + " set " + changeFieldName + "=(select " + changeFieldName + " from " + tableName + " where " + identityName + "=" + identity2ParameterName + ") where " + identityName + "=" + identityParameterName + ";");
            sql.Append("update " + tableName + " set " + changeFieldName + "=@ChangeValue where " + identityName + "=" + identity2ParameterName + ";");
            return Convert.ToBoolean(DataAccess.ExecuteNonQuery(sql.ToString(), parameters));
        }

        /// <summary>
        /// 取得某个字段的最大值,字段必须为int型
        /// </summary>
        /// <param name="tableName">数据表名</param>
        /// <param name="fieldName">要取得的字段名</param>
        /// <param name="where">条件语句</param>
        /// <param name="parameters">sql语句参数</param>
        /// <returns>最大值,如果没有,则为0</returns>
        public static int GetMaxField(string tableName, string fieldName, string where, params IDataParameter[] parameters)
        {
            if (string.IsNullOrEmpty(tableName) || string.IsNullOrEmpty(fieldName))
                throw new Exception("tableName和fieldName参数不能为空。");

            string sqlStr = "declare @MaxField int;select @MaxField =max(" + fieldName + ") from " + tableName;
            if (!string.IsNullOrEmpty(where))
                sqlStr += " where " + where;
            sqlStr += ";if @MaxField is null begin set @MaxField =0 end;";
            sqlStr += "select @MaxField;";
            return Convert.ToInt32(DataAccess.ExecuteScalar(sqlStr, parameters));
        }

        /// <summary>
        /// 更改某个字段的值，要更改的字段值参数为 @ + fieldName
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="fieldNames">要更改的字段名，多个字段之间用“,”隔开</param>
        /// <param name="where">条件语句</param>
        /// <param name="parameters">执行参数，至少要有一个参数即要更改字段的值参数</param>
        /// <returns>成功返回true，失败返回false</returns>
        public static bool UpdateFields(string tableName, string fieldNames, string where, params IDataParameter[] parameters)
        {
            if (string.IsNullOrEmpty(tableName) || string.IsNullOrEmpty(fieldNames))
                throw new Exception("tableName和fieldName参数不能为空。");

            string sql = "update " + tableName + " set ";
            string[] list = fieldNames.Split(',');
            foreach (string fieldName in list)
                sql +=fieldName + "=" + AdoHelper().BuildParameterName(fieldName) + ",";
            sql = sql.TrimEnd(',');
            if (!string.IsNullOrEmpty(where))
                sql += " where " + where;
            return Convert.ToBoolean(DataAccess.ExecuteNonQuery(sql, parameters));
        }

        /// <summary>
        /// 根据条件取得一条记录的某个字段的值
        /// </summary>
        /// <param name="table">表名</param>
        /// <param name="fieldName">要取得值的字段名</param>
        /// <param name="where">条件语句</param>
        /// <param name="parameters">sql执行参数</param>
        /// <returns>字段的值</returns>
        public static object GetFieldValue(string table, string fieldName, string where, IDataParameter[] parameters)
        {
            if (string.IsNullOrEmpty(table) || string.IsNullOrEmpty(fieldName))
                throw new Exception("tableName和fieldName参数不能为空。");
                
            string sql = "select top(1) " + fieldName + " from " + table;
            if (!string.IsNullOrEmpty(where))
                sql += " where " + where;

            return ExecuteScalar(sql, parameters);
        }

        /// <summary>
        /// 分页形式取得记录集
        /// </summary>
        /// <param name="tableName">数据表名</param>
        /// <param name="declare">sql语句执行前的声明 例如：declare @Id int;set @Id=5;</param>
        /// <param name="fields">要查询的字段，所有字段用*</param>
        /// <param name="where">条件语句</param>
        /// <param name="orderField">排序字段，可以使用多字段排序(注：排序字段不能为空)。例如： DateCreated desc,Id asc</param>
        /// <param name="pageIndex">页数</param>
        /// <param name="pageSize">每页记录条数，如果小于1则表示显示所有记录</param>
        /// <param name="totalRecord">总记录条数</param>
        /// <returns>返回页数记录集</returns>
        public static IDataReader GetDataReader(string tableName,string declare, string fields, string where, string orderByField, int pageIndex, int pageSize, out int totalRecord, params IDataParameter[] parameters)
        {
            if (string.IsNullOrEmpty(tableName) || string.IsNullOrEmpty(orderByField))
                throw new Exception("tableName和orderField参数不能为空。");

            if (pageSize <= 0)
                throw new Exception("每页记录条数不能设置为小于1的数值。");
    
            totalRecord = DataAccess.GetRecord(declare,tableName, where, parameters);

            if (string.IsNullOrEmpty(fields))
                fields = "*";

            int pageRecord = (int)Math.Ceiling((decimal)((decimal)totalRecord / (decimal)pageSize));

            if (pageIndex < 1)
                pageIndex = 1;
            if (pageIndex > pageRecord)
                pageIndex = pageRecord;

            orderByField = orderByField.ToLower().Replace("order by", string.Empty).Trim();

            StringBuilder sql = new StringBuilder();
            if (!string.IsNullOrEmpty(declare))
                sql.Append(declare);
            sql.Append("select * from (");
            sql.Append("select ROW_NUMBER()Over(order by " + orderByField + ") as rowId," + fields + " from " + tableName);
            if (!string.IsNullOrEmpty(where))
                sql.Append(" where " + where);
            
            sql.Append(") as myTable");
            sql.Append(" where rowId between " + ((pageIndex - 1) * pageSize + 1) + " and " + pageIndex * pageSize + ";");

            return DataAccess.ExecuteReader(sql.ToString(), parameters);
        }

        /// <summary>
        /// 备份数据库
        /// </summary>
        /// <param name="path">备份文件路径</param>
        /// <param name="dataBaseName">数据库名称</param>
        public static void BackupDataBase(string path, string dataBaseName)
        {
            string sql = "backup database " + dataBaseName + " to disk=@Path";
            IDataParameter parameter = CreateParameter("Path", path);
            DataAccess.ExecuteNonQuery(sql, parameter);
        }

        #endregion

        #region Utility

        /// <summary>
        /// 生成IDataParameter
        /// </summary>
        /// <param name="parameterName">IDataParameter名称</param>
        /// <param name="value">IDataParameter值</param>
        /// <returns>IDataParameter</returns>
        public static IDataParameter CreateParameter(string parameterName, object value)
        {
            if (string.IsNullOrEmpty(parameterName))
                throw new Exception("parameterName参数不能为空。");
            
            IDataParameter parameter = DataAccess.AdoHelper().DbProviderFactory.CreateParameter();
            parameter.ParameterName = DataAccess.AdoHelper().BuildParameterName(parameterName);
            parameter.Value = (value == null || (value is string && string.IsNullOrEmpty(value as string)))
                ? DBNull.Value
                : (object)value;

            return parameter;
        }

        #endregion
    }
}
